﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace frmMain
{
    public partial class Form1 : Form
    {
        delegate int Calculate(int x, int y);
        public Form1()
        {
            InitializeComponent();
        }
        void calculate()
        {
            //Expression 
            Calculate exp = (x, y) => x + y;

            //statement block
            Calculate bl = (x, y) =>
            {
                return x + y;
            };

            //Create with anonymous method
            Func<int, bool> del = delegate(int a)
            {
                return a % 2 == 0;
            };

            //Create with lambda expression even numbers
            Func<int, bool> del1 = a => a % 2 == 0;
            Func<int, int, int> calculate = (x, y) => x + y;
            int KQ = calculate.Invoke(2, 4);

            Dictionary<int, string> dic = new Dictionary<int, string>
            {
                {0,"Zero"},{1,"One"},{2,"Two"},{3,"Three"},{4,"Four"},{5,"Five"},{6,"Six"},{7,"Seven"},{8,"Eight"},{9,"Nine"}
            };
            var query = from item in dic
                        where (item.Key % 2) == 0
                        select item;

            int[] numbers = new int[5] { 0, 1, 2, 3, 4 };
            //Query syntax
            IEnumerable query1 = from num in numbers
                                 where (num % 2) == 0
                                 orderby num descending
                                 select num;

            //Method syntax and lambda expression
            var query2 = numbers
                .Where(num => num % 2 == 0)
                .OrderBy(num => num)
                .Select(num => num);
        }

        #region List
        List<PHIM> DSFILM = new List<PHIM>()
        {
            new PHIM{MAFILM="FILM01", TENFILM="HARRY POTTER VA CHIEC COC LUA", THELOAI="TL01", SOTAP=2},
            new PHIM{MAFILM="FILM02", TENFILM="BAC SY NHAN AI", THELOAI="TL02", SOTAP=20},
            new PHIM{MAFILM="FILM03", TENFILM="HON MA TRONG DEM", THELOAI="TL03", SOTAP=1},
            new PHIM{MAFILM="FILM04", TENFILM="KIEP VO BE", THELOAI="TL02", SOTAP=2},
            new PHIM{MAFILM="FILM05", TENFILM="PETTER SPAN", THELOAI="TL01", SOTAP=2},
            new PHIM{MAFILM="FILM06", TENFILM="SPIDER MAN", THELOAI="TL01", SOTAP=2},
            new PHIM{MAFILM="FILM07", TENFILM="MUA MUA HE", THELOAI="TL02", SOTAP=50},
            new PHIM{MAFILM="FILM08", TENFILM="CAN NHA SO 13", THELOAI="TL03", SOTAP=30},
            new PHIM{MAFILM="FILM09", TENFILM="TRUONG DUA", THELOAI="TL04", SOTAP=2},
            new PHIM{MAFILM="FILM10", TENFILM="CHIEC KHAN TAY", THELOAI="TL02", SOTAP=10},
            new PHIM{MAFILM="FILM11", TENFILM="NGUOI IZA", THELOAI="TL01", SOTAP=30},
            new PHIM{MAFILM="FILM12", TENFILM="TELEPORT", THELOAI="TL01", SOTAP=10}
        };
        List<THELOAI> DSTL = new List<THELOAI>() {
            new THELOAI{MATHELOAI="TL01", TENTHELOAI = "VIEN TUONG"},
            new THELOAI{MATHELOAI="TL02", TENTHELOAI = "TINH CAM"},
            new THELOAI{MATHELOAI="TL03", TENTHELOAI = "KINH DI"},
            new THELOAI{MATHELOAI="TL04", TENTHELOAI = "HANH DONG"},
            new THELOAI{MATHELOAI="TL05", TENTHELOAI = "18+"},
        };


        List<HOCSINH> DSHS = new List<HOCSINH>()
            {
                new HOCSINH{MAHS="HS01",HOHS="HỒ CÔNG",TENHS="SƠN",LOP="LOP01"},
                new HOCSINH{MAHS="HS02",HOHS="LÊ THANH",TENHS="SƠN",LOP="LOP02"},
                new HOCSINH{MAHS="HS03",HOHS="PHÚC BẢO",TENHS="TRÂN",LOP="LOP01"},
                new HOCSINH{MAHS="HS04",HOHS="TRỊNH YẾN",TENHS="TRÂN",LOP="LOP02"},
                new HOCSINH{MAHS="HS05",HOHS="BẢO PHÚC",TENHS="LỢI",LOP="LOP01"},
                new HOCSINH{MAHS="HS06",HOHS="HI",TENHS="SANG",LOP="LOP02"},
                new HOCSINH{MAHS="HS07",HOHS="ĐINH KHÔNG",TENHS="THẲNG",LOP="LOP01"},
                new HOCSINH{MAHS="HS08",HOHS="ĐINH KHÔNG",TENHS="CONG",LOP="LOP02"},
                new HOCSINH{MAHS="HS09",HOHS="NGUYÊN BẢO",TENHS="NGỌC",LOP="LOP01"},
                new HOCSINH{MAHS="HS10",HOHS="PHẠM HOÀNG TRÚC",TENHS="MAI",LOP="LOP02"},
                new HOCSINH{MAHS="HS11",HOHS="PHẠM TRUNG",TENHS="NGỌC",LOP="LOP01"},
            };
        List<LOP> DSLOP = new List<LOP>()
        {
            new LOP{MALOP="LOP01",TENLOP="LINQ"},
            new LOP{MALOP="LOP02",TENLOP="SQL"},
            new LOP{MALOP="LOP03",TENLOP="C#"}
        };

        #endregion

        private void btnOK_Click(object sender, EventArgs e)
        {
            DGV_Result.DataSource = null;

            //TRUY VẤN LinQ
            var query = from hs in DSHS
                        where hs.LOP=="LOP02"
                        orderby hs.MAHS
                        select hs;

            DGV_Result.DataSource = query.ToList();
        }

        private void btnLinQ2_Click(object sender, EventArgs e)
        {
            DGV_Result.DataSource = null;
            var query = from hs in DSHS
                        where hs.LOP == "LOP02"
                        orderby hs.MAHS
                        select new {hs.MAHS, hs.TENHS};//kieu tra ve chi co 2 thuoc tinh ma va tenhs

            DGV_Result.DataSource = query.ToList();
        }

        private void btnLinQ3_Click(object sender, EventArgs e)
        {
            DGV_Result.DataSource = null;

            //TRUY VẤN LinQ
            var query = from hs in DSHS
                        join lop in DSLOP on hs.LOP equals lop.MALOP
                        orderby hs.MAHS
                        select new {hs.MAHS, hs.HOHS, hs.TENHS, lop.TENLOP };

            DGV_Result.DataSource = query.ToList();
        }

        private void btnLinQ4_Click(object sender, EventArgs e)
        {
            DGV_Result.DataSource = null;

            //TRUY VẤN LinQ
            var query = from lop in DSLOP
                        join hs in DSHS on lop.MALOP equals hs.LOP into lop2
                        from q in lop2.DefaultIfEmpty()
                        select new { lop.MALOP, lop.TENLOP, MAHS=(q==null)?"Chưa có học sinh":q.MAHS};

            DGV_Result.DataSource = query.ToList();
        }

        private void btnLinQ5_Click(object sender, EventArgs e)
        {
            DGV_Result.DataSource = null;

            //TRUY VẤN LinQ
            var query = from lop in DSLOP
                        join hs in DSHS on lop.MALOP equals hs.LOP
                        group hs by new { lop.MALOP, lop.TENLOP} into g
                        select new { MALOP = g.Key.MALOP, TENLOP= g.Key.TENLOP, SOHS = g.Count() };


            DGV_Result.DataSource = query.ToList();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            var query = DSFILM
                .Join
                    (DSTL,
                        f => f.THELOAI,
                        tl => tl.MATHELOAI,
                        (f, tl) => new { f.MAFILM, f.TENFILM, tl.TENTHELOAI, f.SOTAP }
                    );

            DGV_Result.DataSource = query.ToList();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            var query = DSTL
               .GroupJoin
                   (DSFILM,
                       tl => tl.MATHELOAI,
                       f => f.THELOAI,
                       (tl, qh) => new { tl, qh }
                   )
               .SelectMany(f => f.qh.DefaultIfEmpty(),
               (selectTL, f) => new
               {
                   MATHELOAI = selectTL.tl.MATHELOAI,
                   TENTHELOAI = selectTL.tl.TENTHELOAI,
                   TENFILM = (f == null) ? "Chua co film" : f.TENFILM,
                   SOTAP = (f == null) ? 0 : f.SOTAP
               });

            DGV_Result.DataSource = query.ToList();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            var query = DSTL
                .GroupJoin
                (
                    DSFILM,
                    tl => tl.MATHELOAI,
                    f => f.THELOAI,
                    (tl, qh) => new { tl, qh }
                )
                .GroupBy(f => new {f.tl.MATHELOAI, f.tl.TENTHELOAI })
                .SelectMany(f => f.DefaultIfEmpty(),
                    (tl, f) => new
                    {
                        f.tl.MATHELOAI,
                        f.tl.TENTHELOAI,
                        SOLUONGFILM = (f == null) ? 0 : f.qh.Count(),
                        TONGSOTAP = (f == null) ? 0 : f.qh.Sum(s=>s.SOTAP)
                    }
                );

            DGV_Result.DataSource = query.ToList();
        }

        
        
    }

    class HOCSINH
    {
        public string MAHS { get; set; }
        public string HOHS { get; set; }
        public string TENHS { get; set; }
        public string LOP { get; set; }
    }
    class LOP
    {
        public string MALOP { get; set; }
        public string TENLOP { get; set; }
    }
    class PHIM
    {
        public string MAFILM { get; set; }
        public string TENFILM { get; set; }
        public string THELOAI { get; set; }
        public int SOTAP { get; set; }
    }
    class THELOAI
    {
        public string MATHELOAI { get; set; }
        public string TENTHELOAI { get; set; }
    }
}
